package com.sec.etech.bpm.stat.manager.impl;

import com.sec.etech.bpm.stat.manager.BpmStatManager;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Service
public class BpmStatManagerImpl implements BpmStatManager {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> queryApplyCounts(Map<String, String> mapParam) {
        // bpm_instance
        List list = null;
        String select = null;
        String where = null;
        String groupBy = null;
        String statKey = mapParam.get("statKey");
        if("groupByDefKey".equals(statKey)){
            select = "select def_key_ as defKey, def_name_ as name, count(id_) as count ";
            groupBy = "group by def_key_, def_name_";
        }else if("groupByOrg".equals(statKey)){
            // TODO
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";

        }else if("groupByUser".equals(statKey)){
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";
        }else{
            return list;
        }

        select += " FROM bpm_instance inst";
        where = " where 1=1 ";
        //"defKey", "orgId","startTime","endTime","orderBy"
        if(mapParam.containsKey("defKey")){
            where += " and def_key_='"+mapParam.get("defKey")+"'";
        }
        if(mapParam.containsKey("orgId")){
            // TODO
        }
        if(mapParam.containsKey("startTime")){
            where += " and create_time_>'STR_TO_DATE('"+mapParam.get("startTime")+" 00:00:00', '%Y-%m-%d %H:%i:%s')";
        }
        if(mapParam.containsKey("endTime")){
            where += " and create_time_>='STR_TO_DATE('"+mapParam.get("endTime")+" 23:59:59', '%Y-%m-%d %H:%i:%s')";
        }
        String sql = select + where + groupBy;
        if(mapParam.containsKey("orderBy")){
            sql += " order by count(id_) " + mapParam.get("orderBy");
        }
        if(mapParam.containsKey("limit")){
            sql += " limit " + mapParam.get("limit");
        }
        list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> queryApproveCounts(Map<String, String> mapParam) {
        // bpm_task_opinion + bpm_instance
        List list = null;
        String select = null;
        String where = null;
        String groupBy = null;
        String statKey = mapParam.get("statKey");
        if("groupByDefKey".equals(statKey)){
            // TODO
            select = "select i.def_key_ as defKey, i.def_name_ as name, count(t.task_id_) as count ";
            select += " FROM bpm_task_opinion t inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = "group by i.def_key_, i.def_name_";
        }else if("groupByGroup".equals(statKey)){
            // TODO
            select = "select approver_ as user, approver_name_ as name, count(t.task_id_) as count ";
            select += " FROM bpm_task_opinion t  inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = "group by approver_, approver_name_";

        }else if("groupByUser".equals(statKey)){
            select = "select approver_ as user, approver_name_ as name, count(t.task_id_) as count ";
            select += " FROM bpm_task_opinion t  inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = "group by approver_, approver_name_";
        }else{
            return list;
        }


        where = " where t.task_id_!=0 ";
        //"defKey", "orgId","startTime","endTime","orderBy"
        if(mapParam.containsKey("defKey")){
            where += " and i.def_key_='"+mapParam.get("defKey")+"'";
        }
        if(mapParam.containsKey("orgId")){
            // TODO
        }
        if(mapParam.containsKey("startTime")){
            where += " and t.approve_time_>'STR_TO_DATE('"+mapParam.get("startTime")+" 00:00:00', '%Y-%m-%d %H:%i:%s')";
        }
        if(mapParam.containsKey("endTime")){
            where += " and t.approve_time_>='STR_TO_DATE('"+mapParam.get("endTime")+" 23:59:59', '%Y-%m-%d %H:%i:%s')";
        }
        String sql = select + where + groupBy;
        if(mapParam.containsKey("orderBy")){
            sql += " order by count(t.task_id_) " + mapParam.get("orderBy");
        }
        if(mapParam.containsKey("limit")){
            sql += " limit " + mapParam.get("limit");
        }
        list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> queryApplyAvgTimes(Map<String, String> mapParam) {
        // TODO
        List list = null;
        String select = null;
        String where = null;
        String groupBy = null;
        String statKey = mapParam.get("statKey");
        if("groupByDefKey".equals(statKey)){
            select = "select def_key_ as defKey, def_name_ as name, count(id_) as count ";
            groupBy = "group by def_key_, def_name_";
        }else if("groupByOrg".equals(statKey)){
            // TODO
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";

        }else if("groupByUser".equals(statKey)){
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";
        }else{
            return list;
        }

        select += " FROM bpm_instance inst";
        where = " where 1=1 ";
        //"defKey", "orgId","startTime","endTime","orderBy"
        if(mapParam.containsKey("defKey")){
            where += " and def_key_='"+mapParam.get("defKey")+"'";
        }
        if(mapParam.containsKey("orgId")){
            // TODO
        }
        if(mapParam.containsKey("startTime")){
            where += " and create_time_>'STR_TO_DATE('"+mapParam.get("startTime")+" 00:00:00', '%Y-%m-%d %H:%i:%s')";
        }
        if(mapParam.containsKey("endTime")){
            where += " and create_time_>='STR_TO_DATE('"+mapParam.get("endTime")+" 23:59:59', '%Y-%m-%d %H:%i:%s')";
        }
        String sql = select + where + groupBy;
        if(mapParam.containsKey("orderBy")){
            sql += " order by count(id_) " + mapParam.get("orderBy");
        }
        if(mapParam.containsKey("limit")){
            sql += " limit " + mapParam.get("limit");
        }
        list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> queryApproveAvgTimes(Map<String, String> mapParam) {
        // TODO  bpm_task_opinion
        // bpm_task_opinion + bpm_instance
        List list = null;
        String select = null;
        String where = null;
        String groupBy = null;
        String statKey = mapParam.get("statKey");
        if("groupByNodeId".equals(statKey)){
            // TODO
            select = "select t.task_key_ as taskKey, t.task_name_ as name, avg(t.dur_ms_)/3600000 as avgTime";
            select += " FROM bpm_task_opinion t inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = " group by t.task_key_, t.task_name_";
        }else if("groupByGroup".equals(statKey)){
            // TODO
            select = "select approver_ as user, approver_name_ as name, avg(t.dur_ms_)/3600000 as avgTime ";
            select += " FROM bpm_task_opinion t  inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = " group by approver_, approver_name_";

        }else if("groupByUser".equals(statKey)){
            select = "select approver_ as user, approver_name_ as name, avg(t.dur_ms_)/3600000 as avgTime ";
            select += " FROM bpm_task_opinion t  inner join bpm_instance i on i.id_ = t.inst_id_";
            groupBy = "  group by approver_, approver_name_";
        }else{
            return list;
        }


        where = " where t.task_id_!=0 and t.status_!='awaiting_check' ";

        if(mapParam.containsKey("defKey")){
            where += " and i.def_key_='"+mapParam.get("defKey")+"'";
        }
        if(mapParam.containsKey("orgId")){
            // TODO
        }
        if(mapParam.containsKey("startTime")){
            where += " and t.approve_time_>'STR_TO_DATE('"+mapParam.get("startTime")+" 00:00:00', '%Y-%m-%d %H:%i:%s')";
        }
        if(mapParam.containsKey("endTime")){
            where += " and t.approve_time_>='STR_TO_DATE('"+mapParam.get("endTime")+" 23:59:59', '%Y-%m-%d %H:%i:%s')";
        }
        String sql = select + where + groupBy;
        if(mapParam.containsKey("orderBy")){
            sql += " order by avg(t.dur_ms_) " + mapParam.get("orderBy");
        }
        if(mapParam.containsKey("limit")){
            sql += " limit " + mapParam.get("limit");
        }
        list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> queryFinishedRate(Map<String, String> mapParam) {
        // TODO
        List list = null;
        String select = null;
        String where = null;
        String groupBy = null;
        String statKey = mapParam.get("statKey");
        if("groupByDefKey".equals(statKey)){
            select = "select def_key_ as defKey, def_name_ as name, count(id_) as count ";
            groupBy = "group by def_key_, def_name_";
        }else if("groupByOrg".equals(statKey)){
            // TODO
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";

        }else if("groupByUser".equals(statKey)){
            select = "select create_by_ as user, creator_ as name, count(id_) as count ";
            groupBy = "group by create_by_, creator_";
        }else{
            return list;
        }

        select += " FROM bpm_instance inst";
        where = " where 1=1 ";
        //"defKey", "orgId","startTime","endTime","orderBy"
        if(mapParam.containsKey("defKey")){
            where += " and def_key_='"+mapParam.get("defKey")+"'";
        }
        if(mapParam.containsKey("orgId")){
            // TODO
        }
        if(mapParam.containsKey("startTime")){
            where += " and create_time_>'STR_TO_DATE('"+mapParam.get("startTime")+" 00:00:00', '%Y-%m-%d %H:%i:%s')";
        }
        if(mapParam.containsKey("endTime")){
            where += " and create_time_>='STR_TO_DATE('"+mapParam.get("endTime")+" 23:59:59', '%Y-%m-%d %H:%i:%s')";
        }
        String sql = select + where + groupBy;
        if(mapParam.containsKey("orderBy")){
            sql += " order by count(id_) " + mapParam.get("orderBy");
        }
        if(mapParam.containsKey("limit")){
            sql += " limit " + mapParam.get("limit");
        }
        list = jdbcTemplate.queryForList(sql);
        return list;
    }
}
